﻿using Microsoft.Extensions.Configuration;
using RuoVea.ExLog;
using RuoVea.ExUtil;
using SqlSugar;

namespace DatabaseMigrationTool
{
    public class DatabaseMigrator
    {
        private readonly Dictionary<DatabaseType, string> _connectionStrings;

        public DatabaseMigrator(Dictionary<DatabaseType, string> connectionStrings)
        {
            _connectionStrings = connectionStrings;
        }

        public void Migrate(DatabaseType sourceType, DatabaseType targetType, string tableName = null, bool overwrite = false)
        {
            try
            {
                Console.WriteLine($"开始从 {sourceType} 迁移到 {targetType}...");

                using (var sourceDb = GetDbInstance(sourceType))
                using (var targetDb = GetDbInstance(targetType))
                {
                    // 获取所有表或指定表
                    var tables = string.IsNullOrEmpty(tableName)
                        ? sourceDb.DbMaintenance.GetTableInfoList()
                        : sourceDb.DbMaintenance.GetTableInfoList()
                            .Where(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase))
                            .ToList();

                    if (tables == null || tables.Count == 0)
                    {
                        Console.WriteLine("没有找到任何表!");
                        return;
                    }

                    // 在目标数据库创建表结构
                    foreach (var table in tables)
                    {
                        try
                        {
                            Console.WriteLine($"正在处理表: {table.Name}");

                            // 检查表是否已存在
                            bool tableExists = targetDb.DbMaintenance.IsAnyTable(table.Name);
                            if (tableExists)
                            {
                                if (!overwrite)
                                {
                                    Console.WriteLine($"表 {table.Name} 已存在，跳过...");
                                    continue;
                                }
                                Console.WriteLine($"表 {table.Name} 已存在，尝试删除...");
                                targetDb.DbMaintenance.DropTable(table.Name);
                            }

                            // 获取表列信息
                            var columns = sourceDb.DbMaintenance.GetColumnInfosByTableName(table.Name, false);

                            // 生成创建表SQL
                            var createTableSql = GenerateCreateTableSql(table.Name, columns, targetType);

                            // 在目标数据库创建表
                            targetDb.Ado.ExecuteCommand(createTableSql);
                            Console.WriteLine($"表 {table.Name} 结构创建成功");

                            // 迁移数据
                            MigrateTableData(sourceDb, targetDb, table.Name);
                        }
                        catch (Exception ex)
                        {
                            $"处理表 {table.Name} 时出错: {ex.Message}".WriteErrorLine();
                            LogFactory.Error($"处理表 {table.Name} 时出错: {ex.Message}");
                            continue;
                        }
                    }
                }

                Console.WriteLine("迁移完成!");
            }
            catch (Exception ex)
            {
                $"迁移过程中发生错误: {ex.Message}".WriteErrorLine();
                LogFactory.Error($"迁移过程中发生错误: {ex.Message}");
            }
        }

        private void MigrateTableData(ISqlSugarClient sourceDb, ISqlSugarClient targetDb, string tableName)
        {
            try
            {
                Console.WriteLine($"开始迁移表 {tableName} 的数据...");

                // 获取表结构信息
                var columns = targetDb.DbMaintenance.GetColumnInfosByTableName(tableName);
                var dateTimeColumns = columns
                    .Where(c => c.DataType.ToLower().Contains("datetime") ||
                                c.DataType.ToLower().Contains("date") ||
                               c.DataType.ToLower().Contains("timestamp"))
                    .Select(c => c.DbColumnName)
                    .ToList();

                // 识别JSON类型列
                var jsonColumns = columns
                    .Where(c => c.DataType.ToLower().Contains("json"))
                    .Select(c => c.DbColumnName)
                    .ToList();

                // 特殊字符检测
                var specialChars = new[] { '-', ' ', '.', '(', ')', '[', ']', '{', '}' };

                // 构建列名映射，确保特殊列名被正确转义
                var columnMappings = columns.ToDictionary(
                    c => c.DbColumnName,
                    c => mysqlReservedWords.Contains(c.DbColumnName.ToLower()) ||
                         c.DbColumnName.IndexOfAny(specialChars) >= 0
                         ? $"`{c.DbColumnName}`"
                         : c.DbColumnName
                );

                // 获取总行数
                var totalCount = sourceDb.Ado.GetInt($"SELECT COUNT(1) FROM `{tableName}`");
                if (totalCount == 0)
                {
                    Console.WriteLine($"表 {tableName} 没有数据需要迁移");
                    return;
                }

                Console.WriteLine($"共 {totalCount} 条数据需要迁移");

                // 分批迁移数据
                const int batchSize = 200;
                int migratedCount = 0;
                int batchCount = (int)Math.Ceiling((double)totalCount / batchSize);

                for (int i = 0; i < batchCount; i++)
                {
                    try
                    {
                        targetDb.Ado.BeginTran();

                        var data = sourceDb.Ado.SqlQuery<dynamic>($"SELECT * FROM `{tableName}` LIMIT {i * batchSize},{batchSize}");

                        if (data.Count == 0)
                        {
                            targetDb.Ado.CommitTran();
                            continue;
                        }

                        // 构建插入SQL - 确保列名被正确转义
                        var firstRow = (IDictionary<string, object>)data[0];
                        var columnsList = firstRow.Keys.Select(k =>
                            columnMappings.ContainsKey(k) ? columnMappings[k] :
                            mysqlReservedWords.Contains(k.ToLower()) || k.IndexOfAny(specialChars) >= 0 ? $"`{k}`" : k
                        ).ToList();

                        var columnsStr = string.Join(",", columnsList);

                        var values = new List<string>();
                        foreach (var row in data)
                        {
                            var rowDict = (IDictionary<string, object>)row;
                            var rowValues = new List<string>();

                            foreach (var col in firstRow.Keys)
                            {
                                var colName = col;
                                var value = rowDict[colName];

                                if (value == null)
                                {
                                    rowValues.Add("NULL");
                                }
                                else if (dateTimeColumns.Contains(colName))
                                {
                                    // 处理日期时间字段
                                    var dateStr = value.ToString();
                                    DateTime dateValue;

                                    // 处理带时区的日期格式
                                    if (dateStr.Contains("+") && DateTime.TryParse(dateStr, null,
                                        System.Globalization.DateTimeStyles.AdjustToUniversal, out dateValue))
                                    {
                                        rowValues.Add($"'{dateValue:yyyy-MM-dd HH:mm:ss}'");
                                    }
                                    // 处理不带时区的日期格式
                                    else if (DateTime.TryParse(dateStr, out dateValue))
                                    {
                                        rowValues.Add($"'{dateValue:yyyy-MM-dd HH:mm:ss}'");
                                    }
                                    else
                                    {
                                        // 如果解析失败，设置为当前时间并记录警告
                                        $"警告：表{tableName}的{colName}字段日期格式解析失败，值：{dateStr}".WriteWarningLine();
                                        //rowValues.Add($"'{DateTime.Now:yyyy-MM-dd HH:mm:ss}'");
                                        LogFactory.Error($"警告：表{tableName}的{colName}字段日期格式解析失败，值：{dateStr}");
                                    }
                                }
                                else if (jsonColumns.Contains(colName))
                                {
                                    // 特殊处理JSON字段
                                    try
                                    {
                                        var jsonStr = value?.ToString();
                                        if (string.IsNullOrWhiteSpace(jsonStr) || jsonStr == "{}")
                                        {
                                            rowValues.Add("'{}'");
                                        }
                                        else
                                        {
                                            /* 方法2：手动转义（备用方案） */
                                            var escapedJson = jsonStr
                                                .Replace("\\", "\\\\")  // 必须先转义反斜杠
                                                .Replace("\"", "\\\"")
                                                .Replace("'", "''")
                                                .Replace("\r", "\\r")
                                                .Replace("\n", "\\n")
                                                .Replace("\t", "\\t")
                                                .Replace("\b", "\\b")
                                                .Replace("\f", "\\f");
                                            rowValues.Add($"'{escapedJson}'");
                                           
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        $"表{tableName}的{colName}字段JSON格式错误，值将被设为空对象: {ex.Message}".WriteWarningLine();
                                        //rowValues.Add("'{}'");
                                        LogFactory.Error($"表{tableName}的{colName}字段JSON格式错误，值将被设为空对象: {ex.Message}");
                                    }
                                }
                                else if (value is string strVal)
                                {
                                    // 处理普通字符串
                                    rowValues.Add($"'{strVal.Replace("'", "''")}'");
                                }
                                else
                                {
                                    // 其他类型直接ToString
                                    rowValues.Add($"'{value}'");
                                }
                            }

                            values.Add($"({string.Join(",", rowValues)})");
                        }

                        // 分批插入，每50条一个批次
                        for (int j = 0; j < values.Count; j += 50)
                        {
                            var batchValues = values.Skip(j).Take(50);
                            var insertSql = $"INSERT INTO `{tableName}` ({columnsStr}) VALUES {string.Join(",", batchValues)}";

                            try
                            {
                                targetDb.Ado.ExecuteCommand(insertSql);
                            }
                            catch (Exception ex)
                            {
                                $"插入数据时出错: {ex.Message}".WriteErrorLine();
                                $"失败SQL: {insertSql}".WriteErrorLine();
                                LogFactory.Error($"插入数据时出错: {ex.Message}  失败SQL: {insertSql}");
                                throw;
                            }
                        }

                        targetDb.Ado.CommitTran();
                        migratedCount += data.Count;
                        Console.WriteLine($"已迁移 {migratedCount}/{totalCount} 条数据");
                    }
                    catch (Exception ex)
                    {
                        targetDb.Ado.RollbackTran();
                        $"迁移批次 {i + 1}/{batchCount} 失败: {ex.Message}".WriteErrorLine();
                        LogFactory.Error($"迁移批次 {i + 1}/{batchCount} 失败: {ex.Message}");

                        // 继续处理下一批数据
                    }
                }

                Console.WriteLine($"表 {tableName} 数据迁移完成");
            }
            catch (Exception ex)
            {
                $"迁移表 {tableName} 数据时出错: {ex.Message}".WriteErrorLine();
                LogFactory.Error($"迁移表 {tableName} 数据时出错: {ex.Message}");
            }
        }

        // 在类中定义扩展的保留字列表
        private readonly HashSet<string> mysqlReservedWords = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
{
    "sql", "default", "key", "uid", "group", "order", "desc", "primary",
    "schema", "table", "select", "insert", "update", "delete", "where",
    "and", "or", "not", "null", "is", "in", "like", "between", "join"
};
        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <param name="targetType"></param>
        /// <returns></returns>
        private string GenerateCreateTableSql(string tableName, List<DbColumnInfo> columns, DatabaseType targetType)
        {
            var sql = $"CREATE TABLE `{tableName}` (";
            var primaryKeys = new List<string>();
            bool hasAutoIncrement = false;

            // 特殊字符检测
            var specialChars = new[] { '-', ' ', '.', '(', ')', '[', ']', '{', '}' };

            foreach (var column in columns)
            {
                // 处理保留字和特殊字符列名
                var columnName = column.DbColumnName;
                if (mysqlReservedWords.Contains(column.DbColumnName.ToLower()) ||
                    column.DbColumnName.IndexOfAny(specialChars) >= 0)
                {
                    columnName = $"`{column.DbColumnName}`";
                }

                // 转换数据类型
                var dataType = ConvertDataType(column.IsPrimarykey, column.DataType, targetType);

                sql += $"{columnName} {dataType}";

                // 处理非空约束
                if (!column.IsNullable)
                {
                    sql += " NOT NULL";
                }

                // 处理自增
                if (column.IsIdentity)
                {
                    if (targetType == DatabaseType.MySQL)
                    {
                        sql += " AUTO_INCREMENT";
                        if (column.IsPrimarykey)
                        {
                            sql += " PRIMARY KEY";
                            hasAutoIncrement = true;
                        }
                    }
                }

                // 收集主键
                if (column.IsPrimarykey && !hasAutoIncrement)
                {
                    primaryKeys.Add(columnName);
                }

                sql += ", ";
            }

            // 添加主键约束(如果没有自增主键)
            if (primaryKeys.Count > 0 && !hasAutoIncrement)
            {
                sql += $"PRIMARY KEY ({string.Join(", ", primaryKeys)}), ";
            }

            // 移除最后的逗号和空格
            sql = sql.TrimEnd(',', ' ');

            sql += ")";

            // MySQL特定设置
            if (targetType == DatabaseType.MySQL)
            {
                sql += " ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
            }

            return sql;
        }

        private string ConvertDataType(bool isPrimarykey, string sourceType, DatabaseType targetType)
        {
            sourceType = sourceType?.ToLower() ?? "text";

            if (targetType == DatabaseType.SQLite)
            {
                return sourceType switch
                {
                    var t when t.Contains("int") => "INTEGER",
                    var t when t.Contains("char") || t.Contains("text") => "TEXT",
                    var t when t.Contains("blob") => "BLOB",
                    var t when t.Contains("real") || t.Contains("double") || t.Contains("float") => "REAL",
                    var t when t.Contains("bool") => "INTEGER",
                    var t when t.Contains("date") || t.Contains("time") => "TEXT",
                    var t when t.Contains("json") => "TEXT",
                    _ => "TEXT"
                };
            }
            else if (targetType == DatabaseType.MySQL)
            {
                return sourceType switch
                {
                    var t when t.Contains("bigint") => "BIGINT",
                    var t when t.Contains("int") && isPrimarykey => "BIGINT",
                    var t when t.Contains("int") => "INT",
                    var t when t.Contains("smallint") => "SMALLINT",
                    var t when t.Contains("char") => "VARCHAR(255)",
                    var t when t.Contains("text") => "MEDIUMTEXT",
                    var t when t.Contains("blob") => "LONGBLOB",
                    var t when t.Contains("double") => "DOUBLE",
                    var t when t.Contains("float") => "FLOAT",
                    var t when t.Contains("bool") => "TINYINT(1)",
                    //var t when t.Contains("date") => "DATE",
                    var t when t.Contains("date") || t.Contains("time") => "DATETIME",
                    var t when t.Contains("json") => "JSON",
                    _ => "VARCHAR(255)"
                };
            }
            else if (targetType == DatabaseType.PostgreSQL)
            {
                return sourceType switch
                {
                    var t when t.Contains("bigint") => "BIGINT",
                    var t when t.Contains("int") => "INTEGER",
                    var t when t.Contains("smallint") => "SMALLINT",
                    var t when t.Contains("char") => "VARCHAR(255)",
                    var t when t.Contains("text") => "MEDIUMTEXT",
                    var t when t.Contains("blob") => "BYTEA",
                    var t when t.Contains("double") => "DOUBLE PRECISION",
                    var t when t.Contains("float") => "REAL",
                    var t when t.Contains("bool") => "BOOLEAN",
                    var t when t.Contains("date") => "DATE",
                    var t when t.Contains("time") => "TIMESTAMP",
                    var t when t.Contains("json") => "JSONB",
                    _ => "TEXT"
                };
            }

            return "TEXT";
        }

        private SqlSugarScope GetDbInstance(DatabaseType dbType)
        {
            if (!_connectionStrings.TryGetValue(dbType, out string connectionString))
            {
                throw new ArgumentException($"未配置 {dbType} 的连接字符串");
            }

            var db = new SqlSugarScope(new ConnectionConfig()
            {
                ConnectionString = connectionString,
                DbType = GetSugarDbType(dbType),
                IsAutoCloseConnection = true,
            },
            db =>
            {
                db.Aop.OnLogExecuting = (sql, pars) =>
                {
                    Console.WriteLine(sql);
                };
            });

            return db;
        }

        private DbType GetSugarDbType(DatabaseType dbType)
        {
            return dbType switch
            {
                DatabaseType.SQLite => DbType.Sqlite,
                DatabaseType.MySQL => DbType.MySql,
                DatabaseType.PostgreSQL => DbType.PostgreSQL,
                _ => throw new ArgumentException("不支持的数据库类型"),
            };
        }
    }

    public enum DatabaseType
    {
        SQLite,
        MySQL,
        PostgreSQL
    }

    class Program
    {
        static void Main(string[] args)
        {
            var config = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json").Build();
            var connections = new Dictionary<DatabaseType, string>
            {
                { DatabaseType.SQLite, config.GetConnectionString("SQLite") },
                { DatabaseType.MySQL, config.GetConnectionString("MySQL") },
                { DatabaseType.PostgreSQL, config.GetConnectionString("PostgreSQL") }
            };

            var migrator = new DatabaseMigrator(connections);

            while (true)
            {
                Console.WriteLine("\n请选择操作:");
                Console.WriteLine("1. SQLite → MySQL");
                Console.WriteLine("2. SQLite → PostgreSQL");
                Console.WriteLine("3. MySQL → SQLite");
                Console.WriteLine("4. MySQL → PostgreSQL");
                Console.WriteLine("5. PostgreSQL → SQLite");
                Console.WriteLine("6. PostgreSQL → MySQL");
                Console.WriteLine("0. 退出");
                Console.Write("请输入选项: ");

                var choice = Console.ReadLine();
                choice = "1";
                if (choice == "0") break;

                try
                {
                    var (source, target) = choice switch
                    {
                        "1" => (DatabaseType.SQLite, DatabaseType.MySQL),
                        "2" => (DatabaseType.SQLite, DatabaseType.PostgreSQL),
                        "3" => (DatabaseType.MySQL, DatabaseType.SQLite),
                        "4" => (DatabaseType.MySQL, DatabaseType.PostgreSQL),
                        "5" => (DatabaseType.PostgreSQL, DatabaseType.SQLite),
                        "6" => (DatabaseType.PostgreSQL, DatabaseType.MySQL),
                        _ => throw new ArgumentException("无效的选项")
                    };

                    Console.Write("请输入要迁移的表名(留空迁移所有表): ");
                    var tableName = Console.ReadLine();
                    //tableName = "fields";
                    //tableName = "ui_schemas";

                    Console.Write("是否覆盖已存在的表?(y/n, 默认y): ");
                    var overwrite = Console.ReadLine()?.ToLower() == "";

                    if (string.IsNullOrWhiteSpace(tableName))
                    {
                        migrator.Migrate(source, target, overwrite: overwrite);
                    }
                    else
                    {
                        migrator.Migrate(source, target, tableName, overwrite);
                    }
                }
                catch (Exception ex)
                {
                    $"错误: {ex.Message}".WriteErrorLine();
                }
            }
        }
    }
}